﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace ImbedLibrary
{

    /// <summary>
    /// 连接工具类
    /// </summary>
    public class SqlHelper : SQLBaseHelper
    {
        public SqlHelper(string basePath) : base(basePath) { }

        /// <summary>
        /// 创建SqlCommand对象
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="cmdType">SqlCommand执行的类型</param>
        /// <param name="param">可变长度的参数数组</param>
        /// <returns>SqlCommand对象</returns>
        private SqlCommand CreateCommand(string sql, SqlConnection conn, params SqlParameter[] param)
        {
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                if (param != null)
                {
                    cmd.Parameters.AddRange(param);
                }
                conn.Open();
            }
            catch (Exception)
            {
                throw;
            }
            return cmd;
        }


        /// <summary>
        /// 被调用的返回Int类型方法（本类调用）
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="cmdType">SqlCommand执行的类型</param>
        /// <param name="param">可变长度的参数数组</param>
        /// <returns>Int类型数字</returns>
        public int ExecuteNonQuery(string sql, params SqlParameter[] param)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                int count = 0;
                try
                {
                    SqlCommand cmd = CreateCommand(sql, conn, param);

                    count = cmd.ExecuteNonQuery();
                }
                catch (Exception)
                {

                    throw;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
                return count;
            }
        }


        /// <summary>
        /// 执行查询方法
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="sql">SQL语句</param>
        /// <param name="render">委托方法</param>
        /// <param name="param">可变长度参数数组</param>
        /// <returns>泛型集合</returns>
        public override IList<T> ExecuteReader<T>(string sql, IDataRender<T> render, params IDataParameter[] param)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                IList<T> list = new List<T>();
                SqlDataReader reader = null;
                try
                {
                    SqlCommand cmd = CreateCommand(sql, conn, IDataParameterToSqlParameter(param));
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    render(list, reader);
                }
                catch (Exception)
                {

                    throw;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
                return list;
            }
        }


        /// <summary>
        /// 执行查询方法
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="sql">SQL语句</param>
        /// <param name="render">委托方法</param>
        /// <param name="param">可变长度参数数组</param>
        /// <returns>泛型集合</returns>
        public override IList<T> ExecuteReader<T>(string sql, IDataRender<T> render)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                IList<T> list = new List<T>();
                SqlDataReader reader = null;
                try
                {
                    SqlCommand cmd = CreateCommand(sql, conn, null);
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    render(list, reader);
                }
                catch (Exception)
                {

                    throw;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
                return list;
            }
        }

        #region GetTableFiled
        /// <summary>
        /// Get table filed
        /// </summary>
        /// <param name="tableName">table name</param>
        /// <param name="type">COLUMN_NAME or DESCRIPTION</param>
        /// <returns></returns>
        public override List<string> GetTableField(string tableName, string type)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlDataReader reader = null;
                List<string> list = new List<string>();
                try
                {
                    string sql = "SELECT name FROM SysColumns WHERE id=Object_Id('"+tableName+"')";
                    SqlCommand cmd = CreateCommand(sql, conn, null);
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        list.Add(reader["name"].ToString());
                    }
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
                return list;
            }
        }
        #endregion

        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="sql">SQL集合</param>
        /// <param name="param">参数集合</param>
        /// <returns>返回true ：false</returns>
        public bool ExecuteTransaction(IList<string> sql, IList<SqlParameter[]> param)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlTransaction trans = conn.BeginTransaction();
            try
            {
                conn.Open();
                for (int i = 0; i < sql.Count && i < param.Count; i++)
                {
                    SqlCommand cmd = new SqlCommand(sql[i], conn);
                    cmd.Transaction = trans;
                    cmd.Parameters.AddRange(param[i]);
                    cmd.ExecuteNonQuery();
                }
                trans.Commit();
                return true;
            }
            catch (Exception)
            {
                trans.Rollback();
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return false;
        }

        public override DataSet ExecuteReader(string sql)
        {
            throw new NotImplementedException();
        }

        public override int ExecuteNonQuery(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                int count = 0;
                try
                {
                    SqlCommand cmd = CreateCommand(sql, conn,null);
                    count = cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    string msg = e.Message;
                    throw;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
                return count;
            }
        }

        public override int ExcuteScalar(string sql, params IDataParameter[] param)
        {
            using (SqlConnection conn = new SqlConnection (connString))
            {
                int count = 0;
                try
                {
                    SqlCommand cmd = CreateCommand(sql, conn,IDataParameterToSqlParameter(param));
                    count = cmd.ExecuteScalar().ToInt32();
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
                return count;
            }
        }

        #region IDataParameter to SqlParameter
        /// <summary>
        /// 转换IDataParameter[] 为 SqlParameter[]
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        protected SqlParameter[] IDataParameterToSqlParameter(params IDataParameter[] param)
        {
            SqlParameter[] sqlParam = new SqlParameter[param.Length];
            for (int i = 0; i <param.Length ; i++)
            {
                sqlParam[i] = param[i] as SqlParameter;
            }
            return sqlParam;
        }
        #endregion
    }
}
